{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Engine(mysql+pymysql://root:***@120.92.43.237:3306/word?charset=utf8)"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine = create_engine('mysql+pymysql://root:root123ABC@@120.92.43.237:3306/word?charset=utf8')\n",
    "engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>detail_id</th>\n",
       "      <th>order_id</th>\n",
       "      <th>dishes_id</th>\n",
       "      <th>logicprn_name</th>\n",
       "      <th>parent_class_name</th>\n",
       "      <th>dishes_name</th>\n",
       "      <th>itemis_add</th>\n",
       "      <th>counts</th>\n",
       "      <th>amounts</th>\n",
       "      <th>cost</th>\n",
       "      <th>place_order_time</th>\n",
       "      <th>discount_amt</th>\n",
       "      <th>discount_reason</th>\n",
       "      <th>kick_back</th>\n",
       "      <th>add_inprice</th>\n",
       "      <th>add_info</th>\n",
       "      <th>bar_code</th>\n",
       "      <th>picture_file</th>\n",
       "      <th>emp_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2956</td>\n",
       "      <td>417</td>\n",
       "      <td>610062</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>蒜蓉生蚝</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-01 11:05:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/104001.jpg</td>\n",
       "      <td>1442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2958</td>\n",
       "      <td>417</td>\n",
       "      <td>609957</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>蒙古烤羊腿</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-01 11:07:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/202003.jpg</td>\n",
       "      <td>1442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2961</td>\n",
       "      <td>417</td>\n",
       "      <td>609950</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>大蒜苋菜</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-01 11:07:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/303001.jpg</td>\n",
       "      <td>1442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>2966</td>\n",
       "      <td>417</td>\n",
       "      <td>610038</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>芝麻烤紫菜</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-01 11:11:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/105002.jpg</td>\n",
       "      <td>1442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2968</td>\n",
       "      <td>417</td>\n",
       "      <td>610003</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>蒜香包</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-01 11:11:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/503002.jpg</td>\n",
       "      <td>1442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2774</td>\n",
       "      <td>6750</td>\n",
       "      <td>774</td>\n",
       "      <td>610011</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>白饭/大碗</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-10 21:56:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/601005.jpg</td>\n",
       "      <td>1138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2775</td>\n",
       "      <td>6742</td>\n",
       "      <td>774</td>\n",
       "      <td>609996</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>牛尾汤</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-10 21:56:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/201006.jpg</td>\n",
       "      <td>1138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2776</td>\n",
       "      <td>6756</td>\n",
       "      <td>774</td>\n",
       "      <td>609949</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>意文柠檬汁</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-10 22:01:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/404005.jpg</td>\n",
       "      <td>1138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2777</td>\n",
       "      <td>6763</td>\n",
       "      <td>774</td>\n",
       "      <td>610014</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>金玉良缘</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-10 22:03:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/302003.jpg</td>\n",
       "      <td>1138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2778</td>\n",
       "      <td>6764</td>\n",
       "      <td>774</td>\n",
       "      <td>610017</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>酸辣藕丁</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-10 22:04:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/302006.jpg</td>\n",
       "      <td>1138</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2779 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     detail_id order_id dishes_id logicprn_name parent_class_name dishes_name  \\\n",
       "0         2956      417    610062            NA                NA        蒜蓉生蚝   \n",
       "1         2958      417    609957            NA                NA       蒙古烤羊腿   \n",
       "2         2961      417    609950            NA                NA        大蒜苋菜   \n",
       "3         2966      417    610038            NA                NA       芝麻烤紫菜   \n",
       "4         2968      417    610003            NA                NA         蒜香包   \n",
       "...        ...      ...       ...           ...               ...         ...   \n",
       "2774      6750      774    610011            NA                NA       白饭/大碗   \n",
       "2775      6742      774    609996            NA                NA         牛尾汤   \n",
       "2776      6756      774    609949            NA                NA      意文柠檬汁    \n",
       "2777      6763      774    610014            NA                NA        金玉良缘   \n",
       "2778      6764      774    610017            NA                NA        酸辣藕丁   \n",
       "\n",
       "     itemis_add  counts  amounts cost    place_order_time discount_amt  \\\n",
       "0             0     1.0     49.0   NA 2016-08-01 11:05:00           NA   \n",
       "1             0     1.0     48.0   NA 2016-08-01 11:07:00           NA   \n",
       "2             0     1.0     30.0   NA 2016-08-01 11:07:00           NA   \n",
       "3             0     1.0     25.0   NA 2016-08-01 11:11:00           NA   \n",
       "4             0     1.0     13.0   NA 2016-08-01 11:11:00           NA   \n",
       "...         ...     ...      ...  ...                 ...          ...   \n",
       "2774          0     1.0     10.0   NA 2016-08-10 21:56:00           NA   \n",
       "2775          0     1.0     40.0   NA 2016-08-10 21:56:00           NA   \n",
       "2776          0     1.0     13.0   NA 2016-08-10 22:01:00           NA   \n",
       "2777          0     1.0     30.0   NA 2016-08-10 22:03:00           NA   \n",
       "2778          0     1.0     33.0   NA 2016-08-10 22:04:00           NA   \n",
       "\n",
       "     discount_reason kick_back add_inprice add_info bar_code  \\\n",
       "0                 NA        NA           0       NA       NA   \n",
       "1                 NA        NA           0       NA       NA   \n",
       "2                 NA        NA           0       NA       NA   \n",
       "3                 NA        NA           0       NA       NA   \n",
       "4                 NA        NA           0       NA       NA   \n",
       "...              ...       ...         ...      ...      ...   \n",
       "2774              NA        NA           0       NA       NA   \n",
       "2775              NA        NA           0       NA       NA   \n",
       "2776              NA        NA           0       NA       NA   \n",
       "2777              NA        NA           0       NA       NA   \n",
       "2778              NA        NA           0       NA       NA   \n",
       "\n",
       "          picture_file emp_id  \n",
       "0     caipu/104001.jpg   1442  \n",
       "1     caipu/202003.jpg   1442  \n",
       "2     caipu/303001.jpg   1442  \n",
       "3     caipu/105002.jpg   1442  \n",
       "4     caipu/503002.jpg   1442  \n",
       "...                ...    ...  \n",
       "2774  caipu/601005.jpg   1138  \n",
       "2775  caipu/201006.jpg   1138  \n",
       "2776  caipu/404005.jpg   1138  \n",
       "2777  caipu/302003.jpg   1138  \n",
       "2778  caipu/302006.jpg   1138  \n",
       "\n",
       "[2779 rows x 19 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "A = pd.read_sql('select *from meal_order_detail1',con=engine)\n",
    "A"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['detail_id', 'order_id', 'dishes_id', 'logicprn_name',\n",
       "       'parent_class_name', 'dishes_name', 'itemis_add', 'counts', 'amounts',\n",
       "       'cost', 'place_order_time', 'discount_amt', 'discount_reason',\n",
       "       'kick_back', 'add_inprice', 'add_info', 'bar_code', 'picture_file',\n",
       "       'emp_id'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "A.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>counts</th>\n",
       "      <th>amounts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>49.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>48.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2774</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2775</td>\n",
       "      <td>1.0</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2776</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2777</td>\n",
       "      <td>1.0</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2778</td>\n",
       "      <td>1.0</td>\n",
       "      <td>33.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2779 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      counts  amounts\n",
       "0        1.0     49.0\n",
       "1        1.0     48.0\n",
       "2        1.0     30.0\n",
       "3        1.0     25.0\n",
       "4        1.0     13.0\n",
       "...      ...      ...\n",
       "2774     1.0     10.0\n",
       "2775     1.0     40.0\n",
       "2776     1.0     13.0\n",
       "2777     1.0     30.0\n",
       "2778     1.0     33.0\n",
       "\n",
       "[2779 rows x 2 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "A.loc[:,('counts','amounts')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>counts</th>\n",
       "      <th>amounts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>count</td>\n",
       "      <td>2779.000000</td>\n",
       "      <td>2779.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>mean</td>\n",
       "      <td>1.111191</td>\n",
       "      <td>45.337172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>std</td>\n",
       "      <td>0.625428</td>\n",
       "      <td>36.808550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>min</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25%</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>25.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>50%</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>35.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>75%</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>56.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>max</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>178.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            counts      amounts\n",
       "count  2779.000000  2779.000000\n",
       "mean      1.111191    45.337172\n",
       "std       0.625428    36.808550\n",
       "min       1.000000     1.000000\n",
       "25%       1.000000    25.000000\n",
       "50%       1.000000    35.000000\n",
       "75%       1.000000    56.000000\n",
       "max      10.000000   178.000000"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "A.loc[:,('counts','amounts')].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "白饭/大碗        92\n",
       "凉拌菠菜         77\n",
       "谷稻小庄         72\n",
       "麻辣小龙虾        65\n",
       "白饭/小碗        60\n",
       "五色糯米饭(七色)    58\n",
       "芝士烩波士顿龙虾     55\n",
       "焖猪手          55\n",
       "辣炒鱿鱼         53\n",
       "水煮鱼          47\n",
       "Name: dishes_name, dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "A['dishes_name'].value_counts()[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
